package sqlfactory

import (
	"regexp"
	"strings"
	"sync"

	"gitee.com/tomatomeatman/golang-repository/bricks3/model/dbinfo"
	"gitee.com/tomatomeatman/golang-repository/bricks3/utils/gormutil"
	Log "github.com/cihub/seelog"
)

var (
	tableEntityList = map[string]*dbinfo.TableInfo{}
	loadLocks       sync.Mutex // 读写锁
)

/**
 * 查询表名
 * @return bool
 */
func FindTable(dbName string, onlyTable bool) (bool, string, interface{}) {
	var text string
	if onlyTable {
		text = `
			SELECT table_comment AS 'comment', table_name AS 'name'
				FROM information_schema.tables
				WHERE table_schema = ? AND table_type = 'BASE TABLE'
				ORDER BY table_name`
	} else {
		text = `
			SELECT table_comment AS 'comment', table_name AS 'name'
				FROM information_schema.tables
				WHERE table_schema = ?
				ORDER BY table_name`
	}

	var data []*dbinfo.TableInfo
	tx := gormutil.GetGorm(dbName).Raw(text, dbName).Scan(&data)
	if tx.Error != nil {
		Log.Error("查询发生异常:", tx.Error.Error())
		return false, "查询失败: " + tx.Error.Error(), 5002
	}

	result := make([]*dbinfo.TableInfo, 0)
	for _, v := range data {
		v.DbName = dbName
		result = append(result, v)
	}

	return true, "查询成功", result
}

func LoadTableInfo(dbName, tableName string) (bool, string, interface{}) {
	bl, msg, data := FindField(dbName, tableName)
	if !bl {
		return bl, msg, data
	}

	fields := make(map[string]*dbinfo.Field, 0)
	for _, v := range data.([]*dbinfo.Field) {
		fields[v.GsName] = v
	}

	tableInfo := &dbinfo.TableInfo{
		DbName:  dbName,
		Name:    tableName,
		Fields:  fields,
		Columns: data.([]*dbinfo.Field),
	}

	return true, "查询成功", tableInfo
}

func FindTableInfo(dbName, tableName string) (bool, string, interface{}) {
	if dbName == "" || strings.TrimSpace(dbName) == "" {
		return false, "dbName参数缺失", 1001
	}

	if tableName == "" || strings.TrimSpace(tableName) == "" {
		return false, "tableName参数缺失", 1002
	}

	dbName = strings.TrimSpace(dbName)
	tableName = strings.TrimSpace(tableName)

	key := dbName + "@" + tableName

	loadLocks.Lock()
	defer loadLocks.Unlock()

	if result, ok := tableEntityList[key]; ok {
		return true, "查询成功", result
	}

	// 调用 LoadTableInfo 加载数据
	bl, msg, data := LoadTableInfo(dbName, tableName)
	if !bl {
		return bl, msg, data
	}

	// 类型断言确保 data 是 *dbinfo.TableInfo
	entity, ok := data.(*dbinfo.TableInfo)
	if !ok {
		return false, "数据类型错误", 1003
	}

	tableEntityList[key] = entity // 存储最终结果

	return true, "查询成功", entity
}

// IsContainChinese 判断字符串是否包含中文
func IsContainChinese(s string) bool {
	//match, _ := regexp.MatchString(`[\u4e00-\u9fa5]`, s)
	match, _ := regexp.MatchString(`\p{Han}`, s)
	return match
}

// 查询表字段信息
func findFieldSql(dbType string) string {
	return `
SELECT 
	sTableName,
	sName,
	sType,
	iNull,
	iKey,
	iAuto,
	iIndex,
	sDefaultData,
	CASE iMaxLength
	WHEN sType THEN -1
	ELSE iMaxLength
	END AS 'iMaxLength',
	iDecimal,
	iIntLen,
	iDecimalLen,
	sComment,
	sSimplComment,
	iBigTxt
FROM (
	SELECT
		table_name AS 'sTableName',
		COLUMN_NAME AS 'sName',
		DATA_TYPE AS 'sType',
		CASE DATA_TYPE
			WHEN 'datetime' THEN 30
			WHEN 'date' THEN 30
			WHEN 'time' THEN 30
			WHEN 'decimal' THEN SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, LOCATE('(', COLUMN_TYPE) + 1), ',', 1)
			ELSE SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, LOCATE('(', COLUMN_TYPE) + 1), ')', 1)
		END AS iMaxLength,
		CASE DATA_TYPE
			WHEN 'decimal' THEN 1
			ELSE 0
		END AS 'iDecimal',
		CASE DATA_TYPE
			WHEN 'decimal' THEN CONVERT(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, LOCATE('(', COLUMN_TYPE) + 1), ',', 1), UNSIGNED INTEGER) - 1 - CONVERT(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, LOCATE(',', COLUMN_TYPE) + 1), ')', 1), UNSIGNED INTEGER)
			ELSE 0
		END AS 'iIntLen',
		CASE DATA_TYPE
			WHEN 'decimal' THEN CONVERT(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, LOCATE(',', COLUMN_TYPE) + 1), ')', 1), UNSIGNED INTEGER)
			ELSE 0
		END AS iDecimalLen,
		CASE IS_NULLABLE
		WHEN 'NO' THEN 0
		ELSE 1
		END AS 'iNull',
		CASE column_key
		WHEN 'PRI' THEN 1
		ELSE 0
		END AS 'iKey',
		CASE EXTRA
		WHEN 'auto_increment' THEN 1
		ELSE 0
		END AS 'iAuto',
		ordinal_position AS 'iIndex',
		IFNULL(column_default, '<nil>') AS 'sDefaultData',
		COLUMN_Comment AS 'sComment',
		SUBSTRING_INDEX(COLUMN_Comment, '(', 1) AS 'sSimplComment',
		CASE DATA_TYPE
			WHEN 'text' THEN 1
			WHEN 'longtext' THEN 1
			WHEN 'tinytext' THEN 1
			WHEN 'tinyblob' THEN 1
			WHEN 'longblob' THEN 1
			WHEN 'blob' THEN 1
			ELSE 0
		END AS 'iBigTxt'
	FROM information_schema.columns
	WHERE table_schema = ?
	AND table_name = ?
	ORDER BY ordinal_position
) AS temp`
}

// 查询表字段名信息
func findFieldNameSql(dbType string) string {
	if dbType == "mysql" {
		return `
SELECT
	COLUMN_NAME AS 'sName'
FROM information_schema.columns
WHERE table_schema = ?
AND table_name = ?
ORDER BY ordinal_position`
	}

	if dbType == "dm" {
		return ""
	}

	return `
SELECT
	COLUMN_NAME AS 'sName'
FROM information_schema.columns
WHERE table_schema = ?
AND table_name = ?
ORDER BY ordinal_position`
}

/**
 * 取指定节点下的子节点编号
 * @param dbName 数据库名
 * @param tableName 表名
 * @param idName 主键名
 * @param pidName 父节点字段名
 * @param pidValue 父节点值
 * @return
 */
func NewChildIdSql(dbType, tableName, idName, pidName, pidValue string) string {
	var build strings.Builder
	build.WriteString("SELECT CONCAT(pid, LPAD(RIGHT((cur + 1), 2), 2, '0') ) AS newId FROM (")
	build.WriteString(" 		select 0 as cur, 99999998 as nxt, @pid AS pid ")
	build.WriteString(" 		union")
	build.WriteString(" 		select cast(A.${MajorKeyString} AS SIGNED) AS cur, IFNULL(")
	build.WriteString(" 			(select MIN( CAST(B.${MajorKeyString} as signed integer)) from ${DbTableName} AS B")
	build.WriteString(" 				where cast(B.${MajorKeyString} AS SIGNED) > cast(A.${MajorKeyString} AS SIGNED)")
	build.WriteString(" 				and B.${PidKey} = @pid")
	build.WriteString(" 				and CAST(B.${MajorKeyString} as signed integer) != 0")                                             // 过滤字段中含非数字的记录
	build.WriteString(" 				and CAST(B.${MajorKeyString} as signed integer) = CAST(B.${MajorKeyString} as signed integer) +0") // 过滤字段中含非数字的记录
	build.WriteString(" 			), 99999999) AS nxt, @pid AS pid")
	build.WriteString(" 		from ${DbTableName} AS A")
	build.WriteString(" 		where A.${PidKey} = @pid")
	build.WriteString(" 		and CAST(A.${MajorKeyString} as signed integer) != 0")                                             // 过滤字段中含非数字的记录
	build.WriteString(" 		and CAST(A.${MajorKeyString} as signed integer) = CAST(A.${MajorKeyString} as signed integer) +0") // 过滤字段中含非数字的记录
	build.WriteString(" 	) AS D")
	build.WriteString(" WHERE (nxt - cur > 1) or (nxt = 99999999)")
	build.WriteString(" ORDER BY nxt DESC, cur")
	build.WriteString(" LIMIT 0, 1")

	txt := build.String()
	txt = strings.Replace(txt, "${DbTableName}", tableName, -1)
	txt = strings.Replace(txt, "${PidKey}", pidName, -1)
	txt = strings.Replace(txt, "${MajorKeyString}", idName, -1)
	txt = strings.Replace(txt, "@pid", "@"+pidName, -1)

	return txt
}
